/*
 * Copyright (c) 2005, 2014 vacoor
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 */
package org.vacoor.nothing.wdb.servlet;

import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
import org.vacoor.nothing.common.json.Jacksons;
import org.vacoor.nothing.common.misc.util.Characters;
import org.vacoor.nothing.wdb.util.Jdbcs;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.List;

/**
 */
public class DatabaseServlet extends HttpServlet {
    private static final String CONN_SESSION_KEY = DatabaseServlet.class.getName() + ".Connection";
    public static final String CONN_URL_PARAM_NAME = "url";
    public static final String CONN_USERNAME_PARAM_NAME = "u";
    public static final String CONN_PASSWORD_PARAM_NAME = "p";
    public static final int MAX_ROWS = 1000;
    public static final String SQL_PARAM_NAME = "sql";

    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
            resp.setCharacterEncoding("UTF-8");

            Connection conn = getConnection(req, resp);

            if (conn == null) {
                resp.sendError(HttpServletResponse.SC_FORBIDDEN, "No Connection");
            }

            // start
            String path = req.getPathInfo();
            if (path == null) {
                resp.sendRedirect(req.getServletPath() + "/");
                return;
            }

            if ("/".equals(path)) {
                req.getRequestDispatcher("/WEB-INF/db.jsp").forward(req, resp);
                return;
            }

            resp.setContentType("application/json");

            //
            path = path.substring(1);
            String schema = req.getParameter("schema");
            String table = req.getParameter("table");

            /**
             * 获取 Schema 信息
             */
            if ("schemas".equals(path)) {
                Jacksons.serialize(resp.getOutputStream(), Jdbcs.readSchemas(conn));
                return;
            }

            if ("tables".equals(path)) {
                if (schema != null) {
                    Jacksons.serialize(resp.getOutputStream(), Jdbcs.readTables(conn, schema, null, null));
                }
                return;
            }

            if ("columns".equals(path)) {
                if (schema != null && table != null) {
                    Jacksons.serialize(resp.getOutputStream(), Jdbcs.readColumns(conn, schema, table, null));
                }
                return;
            }

            if ("execute".equals(path)) {
                resp.getWriter().write(executeSql(conn, req.getParameter(SQL_PARAM_NAME)));
            }
        } catch (SQLException e) {
            resp.getWriter().write("{" +
                    "\"count\": " + -1 +
                    ", \"msg\":" + Characters.toUnicode(e.getMessage()) +
                    "}");
        }
    }

    Connection getConnection(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        HttpSession session = request.getSession();

        String url = request.getParameter(CONN_URL_PARAM_NAME);
        String username = request.getParameter(CONN_USERNAME_PARAM_NAME);
        String password = request.getParameter(CONN_PASSWORD_PARAM_NAME);

        Connection conn = (Connection) session.getAttribute(CONN_SESSION_KEY);
            /* 如果请求中包含三个参数则更新 Connection */
        if (url != null && username != null && password != null) {
            if (conn != null) {
                Jdbcs.close(conn);
            }

            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
            session.setAttribute(CONN_SESSION_KEY, conn);
        }

        if (conn == null) {
            WebApplicationContext ctx = WebApplicationContextUtils.getWebApplicationContext(getServletContext());
            if (ctx != null) {
                DataSource ds = ctx.getBean(DataSource.class);
                conn = ds.getConnection();
                session.setAttribute(CONN_SESSION_KEY, conn);
            }
        }

        return conn;
    }

    protected String executeSql(Connection conn, String sql) throws SQLException {
        sql = prepareSql(sql);
        if (sql == null) {
            return "";
        }

        long startTime = System.currentTimeMillis();
        Statement st = conn.createStatement();
        st.setMaxRows(MAX_ROWS);

        if (st.execute(sql)) {
            ResultSet rs = st.getResultSet();
            List<Object[]> rows = Jdbcs.resultSetToListArray(rs, null);

            return ("{" + "\"count\": " + -1 + ", \"result\":" + Jacksons.serialize(rows) + "}");
        } else {
            SQLWarning sqlWarning = st.getWarnings();
            String warnings = sqlWarning != null ? sqlWarning.getMessage() : null;
            int updateCount = st.getUpdateCount();

            long totalTime = System.currentTimeMillis();
            String msg = warnings != null ? "警告: " + warnings : "";

            return ("{" + "\"count\": " + updateCount + ", \"msg\":\"" + (msg + ", 用时" + totalTime) + "\"" + "}");
        }
    }

    protected String prepareSql(String input) {
        if (input == null || (input = input.trim()).length() < 1) {
            return null;
        }
        /*
         * function / procedure 需要以 "END;" 结尾, select / update / delete / insert ... 不能以 ";" 结尾
         */
        input = input.toUpperCase();
        input = input.endsWith("END;") || !input.endsWith(";") ? input : input.substring(0, input.length() - 1);
        input = input.endsWith("END") ? input + ";" : input;

        return input;
    }
}
